package net.ojbk.jiaofu.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.ojbk.database.DatabaseConnection;
import net.ojbk.jiaofu.po.SyTask;
public class SyTaskDao {
	static final String tableName="syTask";
	DatabaseConnection dbconn;
	Statement stmt;
	public SyTaskDao(DatabaseConnection dbconn) {
		this.dbconn = dbconn;
		stmt=dbconn.getStatement();
	}
	public boolean addOneRecord(SyTask st) {   //yes
		boolean flag=false;
		if (st == null) return false;
		if (st.getTaskId()==null||st.getUserId()==null||st.getTitle()==null||st.getContent()==null||st.getBeginTime()==null||st.getEndTime()==null)return false;
		try {
			String sql="insert into "
					+ tableName
					+ "(taskId,userId,title,content,beginTime,endTime,taskType,fileFormat,basicInfo,extraInfo)"
					+ " value('"
					+ st.getTaskId()
					+"','"
					+ st.getUserId()
					+"','"
					+ st.getTitle()
					+"','"
					+ st.getContent()
					+"', '"
					+ new java.sql.Timestamp(st.getBeginTime().getTime())
					+"', '"
					+ new java.sql.Timestamp(st.getEndTime().getTime())
					+"',"
					+ st.getTaskType()
					+",'"
					+ st.getFileFormat()
					+"','"
					+ st.getBasicInfo()
					+"','"
					+ st.getExtraInfo()
					+"');";
			System.out.print(sql);
			stmt.executeUpdate(sql);
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	public boolean deleteOneRecord(SyTask st) {     //yes
		if (st == null) return false;
		if (st.getTaskId()==null)return false;
		boolean flag=false;
		try {
			String sql = "DELETE FROM "
					+ tableName
					+ " WHERE "
					+ "taskId='"
					+ st.getTaskId()
					+ "';";
			System.out.print(sql);
			stmt.executeUpdate(sql);
			flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	public boolean updateOneRecord(SyTask st) {   //yes
		if (st == null) return false;
		if (st.getTaskId()==null||st.getUserId()==null||st.getTitle()==null||st.getContent()==null||st.getBeginTime()==null||st.getEndTime()==null)return false;
		boolean flag=false;
		try {
			String sql = "UPDATE "
					+ tableName
					+ " SET "
					+ "taskId='"
					+ st.getTaskId()
					+ "', userId='"
					+ st.getUserId()
					+ "', title='"
					+ st.getTitle()
					+ "', content='"
					+ st.getContent()
					+ "', beginTime='"
					+ st.getBeginTime()
					+ "', endTime='"
					+ st.getEndTime()
				    + "', taskType="
				    + st.getTaskType()
				    + ", fileFormat='"
				    + st.getFileFormat()
				    + "', basicInfo='"
				    + st.getBasicInfo()
				    + "', extraInfo='"
				    + st.getExtraInfo()
				    +"' where taskId='"
				    + st.getTaskId()
					+ "';";
			System.out.println(sql);
			stmt.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	public boolean queryOneRecord(SyTask st) {   //yes
		if (st == null) return false;
		if (st.getTaskId() == null) return false;
		boolean flag=false;
		ResultSet re;
		try {
			String sql="select * from "
					+ tableName
					+ " where taskId= '"
					+ st.getTaskId()
					+ "';";
			System.out.println(sql);
			re = stmt.executeQuery(sql);
			if (re.next()) {
				st.setTaskId(re.getString("taskId"));
				st.setUserId(re.getString("userId"));
				st.setTitle(re.getString("title"));
				st.setContent(re.getString("content"));
				st.setBeginTime(re.getDate("beginTime"));
				st.setEndTime(re.getDate("endTime"));
				st.setTaskType(re.getInt("taskType"));
				st.setFileFormat(re.getString("fileFormat"));
				st.setBasicInfo(re.getString("basicInfo"));
				st.setExtraInfo(re.getString("extraInfo"));
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	public int getCount() { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public int getCountByUserId(String userId) { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public SyTask[] queryAllRecod(){  //yes
		int count = this.getCount();
		SyTask[] syts=new SyTask[count]; 
		ResultSet re;
		SyTask st;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				st=new SyTask();
				st.setTaskId(re.getString("taskId"));
				st.setUserId(re.getString("userId"));
				st.setTitle(re.getString("title"));
				st.setContent(re.getString("content"));
				st.setBeginTime(re.getTimestamp("beginTime"));
				st.setEndTime(re.getTimestamp("endTime"));
				st.setTaskType(re.getInt("taskType"));
				st.setFileFormat(re.getString("fileFormat"));
				st.setBasicInfo(re.getString("basicInfo"));
				st.setExtraInfo(re.getString("extraInfo"));
				syts[i] = st;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syts;
		}
		return syts;
	}
	public SyTask[] queryAllRecodByUserId(String userId){   //yes
		int count = this.getCountByUserId(userId);
		SyTask[] syts=new SyTask[count]; 
		ResultSet re;
		SyTask st;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "' ;";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				st= new SyTask();
				st.setTaskId(re.getString("taskId"));
				st.setUserId(re.getString("userId"));
				st.setTitle(re.getString("title"));
				st.setContent(re.getString("content"));
				st.setBeginTime(re.getTimestamp("beginTime"));
				st.setEndTime(re.getTimestamp("endTime"));
				st.setTaskType(re.getInt("taskType"));
				st.setFileFormat(re.getString("fileFormat"));
				st.setBasicInfo(re.getString("basicInfo"));
				st.setExtraInfo(re.getString("extraInfo"));
				syts[i] = st;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syts;
		}
		return syts;
	}
	public boolean checkOneRecord(String taskId) { // is OK? ：yes
		boolean flag = false;
		ResultSet re;
		try {
			String sql="select * from "
					+ tableName
					+ " where taskId='"
					+ taskId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	public static void main(String args[]) {
		DatabaseConnection dbco=new DatabaseConnection();
		SyTaskDao std=new SyTaskDao(dbco);

	}
}
